using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

/*=====================================================================

  File:      ShoppingCartDb.cs for Adventure Works Cycles Storefront Sample
  Summary:   Middle tier component for manipulating lists of pending items to be purchased.
  Date:	     June 16, 2003

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */
namespace Microsoft.Samples.SqlServer
{
	//*******************************************************
	//
	// ShoppingCartDB Class
	//
	// Business/Data Logic Class that encapsulates all data
	// logic necessary to add/remove/update/purchase items
	// within an AdventureWorks shopping cart.
	//
	//*******************************************************
	public class ShoppingCartDB
	{
		//*******************************************************
		//
		// ShoppingCartDB.GetItems() Method <a name="GetItems"></a>
		//
		// The GetItems method returns a 
		// a forward-only, read-only DataReader.  This returns a list of all
		// items within a shopping cart. 
		//
		// Other relevant sources:
		//     + <a href="usp_ShoppingList.htm" style="color:green">usp_ShoppingList Stored Procedure</a>
		//
		//*******************************************************
		public SqlDataReader GetItems(string cartID)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
                Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = myConnection.CreateCommand();

			myCommand.CommandText = "usp_ShoppingList";

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterCartID = new SqlParameter("@CartID",
															SqlDbType.NVarChar,
															50);

			parameterCartID.Value = cartID;
			myCommand.Parameters.Add(parameterCartID);

			SqlParameter parameterCulture = new SqlParameter("@Culture",
															 SqlDbType.NVarChar,
															 10);

			parameterCulture.Value = CultureInfo.CurrentUICulture.Name;
			myCommand.Parameters.Add(parameterCulture);

			// Return the datareader result
			myConnection.Open();
			return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}

		//*******************************************************
		//
		// ShoppingCartDB.AddItem() Method <a name="AddItem"></a>
		//
		// The AddItem method adds an item into a shopping cart.
		//
		// Other relevant sources:
		//     + <a href="usp_ShoppingCartAddItem.htm" style="color:green">usp_ShoppingCartAddItem Stored Procedure</a>
		//
		//*******************************************************
		public void AddItem(string cartID, int productID, int quantity)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
                Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = new SqlCommand("usp_ShoppingCartAddItem",
												  myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterProductID = new SqlParameter("@ProductID",
															   SqlDbType.Int, 4);

			parameterProductID.Value = productID;
			myCommand.Parameters.Add(parameterProductID);

			SqlParameter parameterCartID = new SqlParameter("@CartID",
															SqlDbType.NVarChar,
															50);

			parameterCartID.Value = cartID;
			myCommand.Parameters.Add(parameterCartID);

			SqlParameter parameterQuantity = new SqlParameter("@Quantity",
															  SqlDbType.Int, 4);

			parameterQuantity.Value = quantity;
			myCommand.Parameters.Add(parameterQuantity);

			// Open the connection and execute the Command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                myConnection.Close();
            }
        }

		//*******************************************************
		//
		// ShoppingCartDB.UpdateItem() Method <a name="UpdateItem"></a>
		//
		// The UpdateItem method updates the quantity of an item
		// in a shopping cart.
		//
		// Other relevant sources:
		//     + <a href="usp_ShoppingCartUpdate.htm" style="color:green">usp_ShoppingCartUpdate Stored Procedure</a>
		//
		//*******************************************************
		public void UpdateItem(string cartID, int productID, int quantity)
		{
			// throw an exception if quantity is a negative number
			if (quantity < 0)
			{
				throw new Exception("Quantity cannot be a negative number");
			}

			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
                Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = new SqlCommand("usp_ShoppingCartUpdate",
												  myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterProductID = new SqlParameter("@ProductID",
															   SqlDbType.Int, 4);

			parameterProductID.Value = productID;
			myCommand.Parameters.Add(parameterProductID);

			SqlParameter parameterCartID = new SqlParameter("@CartID",
															SqlDbType.NVarChar,
															50);

			parameterCartID.Value = cartID;
			myCommand.Parameters.Add(parameterCartID);

			SqlParameter parameterQuantity = new SqlParameter("@Quantity",
															  SqlDbType.Int, 4);

			parameterQuantity.Value = quantity;
			myCommand.Parameters.Add(parameterQuantity);

			// Open the connection and execute the Command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                myConnection.Close();
            }
        }

		//*******************************************************
		//
		// ShoppingCartDB.RemoveItem() Method <a name="RemoveItem"></a>
		//
		// The RemoveItem method removes an item from a
		// shopping cart.
		//
		// Other relevant sources:
		//     + <a href="usp_ShoppingCartRemoveItem.htm" style="color:green">usp_ShoppingCartRemoveItem Stored Procedure</a>
		//
		//*******************************************************
		public void RemoveItem(string cartID, int productID)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
                Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = new SqlCommand("usp_ShoppingCartRemoveItem",
												  myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterProductID = new SqlParameter("@ProductID",
															   SqlDbType.Int, 4);

			parameterProductID.Value = productID;
			myCommand.Parameters.Add(parameterProductID);

			SqlParameter parameterCartID = new SqlParameter("@CartID",
															SqlDbType.NVarChar,
															50);

			parameterCartID.Value = cartID;
			myCommand.Parameters.Add(parameterCartID);

			// Open the connection and execute the Command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                myConnection.Close();
            }
        }

		//*******************************************************
		//
		// ShoppingCartDB.GetItemCount() Method <a name="GetItemCount"></a>
		//
		// The GetItemCount method returns the number of items
		// within a shopping cart.
		//
		// Other relevant sources:
		//     + <a href="usp_ShoppingCartItemCount.htm" style="color:green">usp_ShoppingCartItemCount Stored Procedure</a>
		//
		//*******************************************************
		public int GetItemCount(string cartID)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
                Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = new SqlCommand("usp_ShoppingCartItemCount",
												  myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			SqlParameter parameterCartID = new SqlParameter("@CartID",
															SqlDbType.NVarChar,
															50);

			parameterCartID.Value = cartID;
			myCommand.Parameters.Add(parameterCartID);

			// Add Parameters to SPROC
			SqlParameter parameterItemCount = new SqlParameter("@ItemCount",
															   SqlDbType.Int, 4);

			parameterItemCount.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterItemCount);

			// Open the connection and execute the Command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                myConnection.Close();
            }

            // Return the ItemCount (obtained as out paramter of SPROC)
			return ((int)parameterItemCount.Value);
		}

		//*******************************************************
		//
		// ShoppingCartDB.GetTotal() Method <a name="GetTotal"></a>
		//
		// The GetTotal method returns the total price of all
		// items within the shopping cart.
		//
		// Other relevant sources:
		//     + <a href="usp_ShoppingCartTotal.htm" style="color:green">usp_ShoppingCartTotal Stored Procedure</a>
		//
		//*******************************************************
		// TODO: Switch back to Currency when this works.
		public String GetTotal(string cartID)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
                Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = new SqlCommand("usp_ShoppingCartTotal",
												  myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterCartID = new SqlParameter("@CartID",
															SqlDbType.NVarChar,
															50);

			parameterCartID.Value = cartID;
			myCommand.Parameters.Add(parameterCartID);

			SqlParameter parameterCulture = new SqlParameter("@Culture",
															 SqlDbType.NVarChar,
															 10);

			parameterCulture.Value = CultureInfo.CurrentUICulture.Name;
			myCommand.Parameters.Add(parameterCulture);

			// TODO: Switch back to Currency when this works.
			SqlParameter parameterTotalCost = new SqlParameter("@TotalCost",
															   SqlDbType.
															   NVarChar, 20);

			parameterTotalCost.Direction = ParameterDirection.Output;

			//parameterTotalCost.UdtTypeName = "[AdventureWorks.Currency]";
			myCommand.Parameters.Add(parameterTotalCost);

			// Open the connection and execute the Command
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			finally
			{
				if (myConnection.State != ConnectionState.Closed)
					myConnection.Close();
			}

			// Return the Total
			return (String)parameterTotalCost.Value;
		}

		//*******************************************************
		//
		// ShoppingCartDB.MigrateCart() Method <a name="MigrateCart"></a>
		//
		// The MigrateCart method migrates the items from one
		// cartId to another.  This is used during the login
		// and/or registration process to transfer a user's
		// temporary cart items to a permanent account.
		//
		// Other relevant sources:
		//     + <a href="usp_SetShoppingCartID.htm" style="color:green">usp_SetShoppingCartID Stored Procedure</a>
		//
		//*******************************************************
		public void MigrateCart(String oldCartId, String newCartId)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
                Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = new SqlCommand("usp_SetShoppingCartID",
												  myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter cart1 = new SqlParameter("@OriginalCartId ",
												  SqlDbType.NVarChar, 50);

			cart1.Value = oldCartId;
			myCommand.Parameters.Add(cart1);

			SqlParameter cart2 = new SqlParameter("@NewCartId ",
												  SqlDbType.NVarChar, 50);

			cart2.Value = newCartId;
			myCommand.Parameters.Add(cart2);

			// Open the connection and execute the Command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                myConnection.Close();
            }
        }

		//*******************************************************
		//
		// ShoppingCartDB.EmptyCart() Method <a name="EmptyCart"></a>
		//
		// The EmptyCart method removes all current items within
		// the shopping cart.
		//
		// Other relevant sources:
		//     + <a href="usp_ShoppingCartEmpty.htm" style="color:green">usp_ShoppingCartEmpty Stored Procedure</a>
		//
		//*******************************************************
		public void EmptyCart(string cartID)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
                Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = new SqlCommand("usp_ShoppingCartEmpty",
												  myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter cartid = new SqlParameter("@CartID",
												   SqlDbType.NVarChar, 50);

			cartid.Value = cartID;
			myCommand.Parameters.Add(cartid);

			// Open the connection and execute the Command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                myConnection.Close();
            }
        }

		//*******************************************************
		//
		// ShoppingCartDB.GetShoppingCartId() Method <a name="GetShoppingCartId"></a>
		//
		// The GetShoppingCartId method is used to calculate the
		// "ShoppingCart" ID key used for a tracking a browser.
		//
		// The ShoppingCartID value is either the User's Identity
		// Name (if they are a registered and authenticated user),
		// or a random GUID calculated for guest visitors or
		// customers who have not yet logged in.
		//
		//*******************************************************
		public String GetShoppingCartId()
		{
			// Obtain current HttpContext of ASP+ Request
			System.Web.HttpContext context = System.Web.HttpContext.Current;

			// If the user is authenticated, use their customerId as a permanent shopping cart id
			if (context.User.Identity.Name.Length != 0)
			{
				return context.User.Identity.Name;
			}

			// If user is not authenticated, either fetch (or issue) a new temporary cartID
			if (context.Request.Cookies["AdventureWorks_CartID"] != null)
			{
				return context.Request.Cookies["AdventureWorks_CartID"].Value;
			}
			else
			{
				// Generate a new random GUID using System.Guid Class
				Guid tempCartId = Guid.NewGuid();

				// Send tempCartId back to client as a cookie
				context.Response.Cookies["AdventureWorks_CartID"].Value =
				tempCartId.
																		  ToString();

				// Return tempCartId
				return tempCartId.ToString();
			}
		}

		public SqlDataReader GetProductRecommendationsFromCart(String cartID)
		{
            SqlConnection conn = new SqlConnection(Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = conn.CreateCommand();

			myCommand.CommandText = "usp_ShoppingCartGetProductRecommendations";
			myCommand.CommandType = CommandType.StoredProcedure;

			SqlParameter cartParam = new SqlParameter("@CartID",
													  SqlDbType.NVarChar, 50);

			cartParam.Value = cartID;
			myCommand.Parameters.Add(cartParam);
			conn.Open();
			return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}
	}
}
